package com.example.chat.service.impl;

import com.example.chat.model.MessageTemplate;
import com.example.chat.repository.MessageTemplateRepository;
import com.example.chat.service.ExcelService;
import com.example.chat.type.MessageType;
import com.example.chat.type.PushMessageSystem;
import com.example.chat.utils.ExcelUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description
 * @Date 2022/11/4 15:33
 * @Author pq
 */
@Service
public class ExcelServiceImpl implements ExcelService {

    @Resource
    private MessageTemplateRepository messageTemplateRepository;

    /**
     * @return java.lang.String
     * @Author pq
     * @Description excel数据导入数据库
     * @Date 10:12 2022/11/5
     * @Param [file]
     **/
    @Override
    public String importExcel(MultipartFile file) {
        //创建工作簿对象
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(file.getInputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        //获取工作簿下sheet的个数
        int sheetNum = wb.getNumberOfSheets();
        //遍历所有工作簿,注意工作簿从0开始
        for (int i = 0; i < sheetNum; i++) {
            System.out.println("读取第" + i + 1 + "个sheet");
            XSSFSheet sheet = wb.getSheetAt(i);
            //获取最后一行的行号，从0开始的
            int maxRow = sheet.getLastRowNum();
            //遍历工作簿中所有行 注意从第1行开始找数据，第0行是字段名
            for (int j = 1; j <= maxRow; j++) {
                int maxCell = sheet.getRow(j).getLastCellNum();
                MessageTemplate messageTemplate = new MessageTemplate();
                String id = sheet.getRow(j).getCell(0).getStringCellValue();
                String pushMessageSystem = sheet.getRow(j).getCell(1).getStringCellValue();
                String messageType = sheet.getRow(j).getCell(2).getStringCellValue();
                String theme = sheet.getRow(j).getCell(3).getStringCellValue();
                String template = sheet.getRow(j).getCell(4).getStringCellValue();
                messageTemplate.setId(id);
                messageTemplate.setPushMessageSystem(PushMessageSystem.valueOf(pushMessageSystem));
                messageTemplate.setMessageType(MessageType.valueOf(messageType));
                messageTemplate.setTheme(theme);
                messageTemplate.setTemplate(template);
                messageTemplateRepository.save(messageTemplate);
            }

        }

        return "200";
    }

    /**
     * @return void
     * @Author pq
     * @Description 数据库数据导出到excel
     * @Date 10:11 2022/11/5
     * @Param [out]
     **/
    @Override
    public void exportExcel(OutputStream out, String title, String[] rowName, List<List<String>> dataList) {
        try {
            //创建工作簿
            try (XSSFWorkbook wb = new XSSFWorkbook()) {
                //创建工作表
                XSSFSheet sheet = wb.createSheet("sheet1");
                //产生表格标题行
                XSSFRow rowTitle = sheet.createRow(0);
                //产生标题单元格
                XSSFCell cellTitle = rowTitle.createCell(0);
                cellTitle.setCellValue("title");
                //定义字段名所需列数
                int columnNum = rowName.length;
                //创建字段名所在行
                XSSFRow rowRowName = sheet.createRow(1);
                //将字段名设置到单元格中
                for (int i = 0; i < columnNum; i++) {
                    //创建字段名单元格
                    XSSFCell cellRowName = rowRowName.createCell(i);
                    cellRowName.setCellType(CellType.STRING);
                    //字段名单元格赋值
                    XSSFRichTextString text = new XSSFRichTextString(rowName[i]);
                    cellRowName.setCellValue(text);
                }
                //将数据set到sheet中的单元格
                for (int i = 0; i < dataList.size(); i++) {
                    List<String> obj = dataList.get(i);//遍历每个对象
                    XSSFRow row = sheet.createRow(i + 2);//创建所需的行数
                    for (int j = 0; j < obj.size(); j++) {
                        XSSFCell cell;
                        cell = row.createCell(j, CellType.STRING);
                        cell.setCellValue(obj.get(j));
                    }

                }
                // 让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        XSSFRow currentRow;
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(colNum) != null) {
                            XSSFCell currentCell = currentRow.getCell(colNum);
                            if (currentCell.getCellType() == CellType.STRING) {
                                int length = currentCell.getStringCellValue().getBytes().length;
                                if (columnWidth < length) {
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    if (colNum == 0) {
                        sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                    } else {
                        sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                    }
                }
                wb.write(out);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public void export(OutputStream out, List<?> dataList) {
        if (CollectionUtils.isNotEmpty(dataList)) {
            String[] columnNameArr = ExcelUtils.getColumnNameArr(dataList.get(0).getClass());
            String[][] exportDataArrArr = ExcelUtils.makeExportDataArrArr(dataList);

            //创建工作簿
            try (XSSFWorkbook wb = new XSSFWorkbook()) {
                //创建工作表
                XSSFSheet sheet = wb.createSheet("sheet1");
                //产生表格标题行
                XSSFRow rowTitle = sheet.createRow(0);
                //产生标题单元格
                XSSFCell cellTitle = rowTitle.createCell(0);
                cellTitle.setCellValue("title");
                //定义字段名所需列数
                int columnNum = columnNameArr.length;
                //创建字段名所在行
                XSSFRow rowRowName = sheet.createRow(1);
                //将字段名设置到单元格中
                for (int i = 0; i < columnNum; i++) {
                    //创建字段名单元格
                    XSSFCell cellRowName = rowRowName.createCell(i);
                    cellRowName.setCellType(CellType.STRING);
                    //字段名单元格赋值
                    XSSFRichTextString text = new XSSFRichTextString(columnNameArr[i]);
                    cellRowName.setCellValue(text);
                }
                //将数据set到sheet中的单元格
                for (int i = 0; i < dataList.size(); i++) {
                    String[] objPropertyValueArr = exportDataArrArr[i];//遍历每个对象
                    XSSFRow row = sheet.createRow(i + 2);//创建所需的行数
                    for (int j = 0; j < objPropertyValueArr.length; j++) {
                        XSSFCell cell;
                        cell = row.createCell(j, CellType.STRING);
                        cell.setCellValue(objPropertyValueArr[j]);
                    }

                }
                // 让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        XSSFRow currentRow;
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(colNum) != null) {
                            XSSFCell currentCell = currentRow.getCell(colNum);
                            if (currentCell.getCellType() == CellType.STRING) {
                                int length = currentCell.getStringCellValue().getBytes().length;
                                if (columnWidth < length) {
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    if (colNum == 0) {
                        sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                    } else {
                        sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                    }
                }
                wb.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void test() {
        // 1. 创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 2. 创建工作表
        XSSFSheet sheet = workbook.createSheet("WriterDataTest");
        // 3. 模拟待写入数据
        Map<String, Object[]> data = new TreeMap<>();
        messageTemplateRepository.findAll().forEach(messageTemplate -> {
            data.put(messageTemplate.getId(), new Object[]{messageTemplate.getId(), messageTemplate.getPushMessageSystem(), messageTemplate.getMessageType(), messageTemplate.getTheme(), messageTemplate.getTemplate()});
        });
        //4. 遍历数据写入表中
        Set<String> keySet = data.keySet();
        int rowNum = 0;
        for (String key : keySet) {
            Row row = sheet.createRow(rowNum++);
            Object[] objArr = data.get(key);
            int cellNum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellNum++);
                if (obj instanceof String) {
                    cell.setCellValue((String) obj);
                } else if (obj instanceof Integer) {
                    cell.setCellValue((Integer) obj);
                }
            }
        }
        try {
            File file = new File("C:\\Users\\DBC20221101\\Desktop\\测试导出.xlsx");
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
